<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="as20941">ALTER OPERATOR FAMILY</title><body><p id="sql_command_desc">Changes the definition of an operator family.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">ALTER OPERATOR FAMILY <varname>name</varname> USING <varname>index_method</varname> ADD
  {  OPERATOR <varname>strategy_number</varname> <varname>operator_name</varname> ( <varname>op_type</varname>, <varname>op_type</varname> ) [ FOR SEARCH | FOR ORDER BY <varname>sort_family_name</varname> ]
    | FUNCTION <varname>support_number</varname> [ ( <varname>op_type</varname> [ , <varname>op_type</varname> ] ) ] <varname>funcname</varname> ( <varname>argument_type</varname> [, ...] )
  } [, ... ]

ALTER OPERATOR FAMILY <varname>name</varname> USING <varname>index_method</varname> DROP
  {  OPERATOR <varname>strategy_number</varname> ( <varname>op_type</varname>, <varname>op_type</varname> ) 
    | FUNCTION <varname>support_number</varname> [ ( <varname>op_type</varname> [ , <varname>op_type</varname> ] ) 
  } [, ... ]

ALTER OPERATOR FAMILY <varname>name</varname> USING <varname>index_method</varname> RENAME TO <varname>new_name</varname>

ALTER OPERATOR FAMILY <varname>name</varname> USING <varname>index_method</varname> OWNER TO <varname>new_owner</varname>

ALTER OPERATOR FAMILY <varname>name</varname> USING <varname>index_method</varname> SET SCHEMA <varname>new_schema</varname></codeblock></section><section id="section3"><title>Description</title><p><codeph>ALTER OPERATOR FAMILY</codeph> changes the definition of an operator family. You can add
        operators and support functions to the family, remove them from the family, or change the
        family's name or owner.</p>
      <p>When operators and support functions are added to a family with <codeph>ALTER OPERATOR
          FAMILY</codeph>, they are not part of any specific operator class within the family, but
        are just "loose" within the family. This indicates that these operators and functions are
        compatible with the family's semantics, but are not required for correct functioning of any
        specific index. (Operators and functions that are so required should be declared as part of
        an operator class, instead; see <xref href="CREATE_OPERATOR_CLASS.xml#topic1" type="topic"
          format="dita"/>.) You can drop loose members of a family  from the family at any time, but
        members of an operator class cannot be dropped without dropping the whole class and any
        indexes that depend on it. Typically, single-data-type operators and functions are part of
        operator classes because they are needed to support an index on that specific data type,
        while cross-data-type operators and functions are made loose members of the family.</p>
      <p>You must be a superuser to use <codeph>ALTER OPERATOR FAMILY</codeph>. (This restriction is
        made because an erroneous operator family definition could confuse or even crash the
        server.)</p>
      <p><codeph>ALTER OPERATOR FAMILY</codeph> does not presently check whether the operator family
        definition includes all the operators and functions required by the index method, nor
        whether the operators and functions form a self-consistent set. It is the user's
        responsibility to define a valid operator family.</p>
      <p><codeph>OPERATOR</codeph> and <codeph>FUNCTION</codeph> clauses can appear in any
        order.</p></section><section id="section4"><title>Parameters</title><parml><plentry><pt><varname>name</varname></pt><pd>The name (optionally schema-qualified) of an existing operator family. </pd></plentry><plentry><pt><varname>index_method</varname></pt><pd>The name of the index method this operator family is for. </pd></plentry>
        <plentry>
          <pt><varname>strategy_number</varname></pt>
          <pd>The index method's strategy number for an operator associated with the operator
            family.</pd>
        </plentry>
        <plentry>
          <pt><varname>operator_name</varname></pt>
          <pd>The name (optionally schema-qualified) of an operator associated with the operator
            family.</pd>
        </plentry>
        <plentry>
          <pt>op_type</pt>
          <pd>In an  <codeph>OPERATOR</codeph> clause, the operand data type(s) of the operator, or
              <codeph>NONE</codeph> to signify a left-unary or right-unary operator. Unlike the
            comparable syntax in <codeph>CREATE OPERATOR CLASS</codeph>, the operand data types must
            always be specified. In an <codeph>ADD FUNCTION</codeph> clause, the operand data type(s)
            the function is intended to support, if different from the input data type(s) of the
            function. For B-tree comparison functions it is not necessary to specify <varname>op_type</varname> since the
            function's input data type(s) are always the correct ones to use.  For B-tree sort
            support functions and all functions in GiST, SP-GiST, and GIN operator
            classes, it is necessary to specify the operand data type(s) the function
            is to be used with.</pd> 
        </plentry>
        <plentry>
          <pt><varname>sort_family_name</varname></pt>
          <pd>The name (optionally schema-qualified) of an existing <codeph>btree</codeph> operator
            family that describes the sort ordering associated with an ordering operator.
          </pd>
          <pd>If neither <codeph>FOR SEARCH</codeph> nor <codeph>FOR ORDER BY</codeph> is
            specified, <codeph>FOR SEARCH</codeph> is the default.</pd>
        </plentry>
        <plentry>
          <pt>support_number</pt>
          <pd>The index method's support procedure number for a function associated with the
            operator family.</pd>
        </plentry>
        <plentry>
          <pt>funcname</pt>
          <pd>The name (optionally schema-qualified) of a function that is an index method support
            procedure for the operator family.</pd>
        </plentry>
        <plentry>
          <pt>argument_types</pt>
          <pd>The parameter data type(s) of the function.</pd>
        </plentry><plentry><pt><varname>new_name</varname></pt><pd>The new name of the operator family. </pd></plentry><plentry><pt><varname>new_owner</varname></pt><pd>The new owner of the operator family. </pd></plentry>
        <plentry>
          <pt><varname>new_schema</varname></pt>
          <pd>The new schema for the operator family. </pd>
        </plentry></parml></section><section id="section5"><title>Compatibility</title><p>There is no <codeph>ALTER OPERATOR FAMILY</codeph> statement in the SQL standard. </p></section>
    <section id="section6">
      <title>Notes</title>
      <p>Notice that the <codeph>DROP</codeph> syntax only specifies the "slot" in the operator
        family, by strategy or support number and input data type(s). The name of the operator or
        function occupying the slot is not mentioned. Also, for <codeph>DROP FUNCTION</codeph> the
        type(s) to specify are the input data type(s) the function is intended to support; for
          <codeph>GiST</codeph>, <codeph>SP_GiST</codeph>, and <codeph>GIN</codeph> indexes this
        might have nothing to do with the actual input argument types of the function. </p>
      <p>Because the index machinery does not check access permissions on functions before using
        them, including a function or operator in an operator family is tantamount to granting
        public execute permission on it. This is usually not an issue for the sorts of functions
        that are useful in an operator family.</p>
      <p>The operators should not be defined by SQL functions. A SQL function is likely to be
        inlined into the calling query, which will prevent the optimizer from recognizing that the
        query matches an index.</p>
      <p>Before Greenplum Database 6.0, the <codeph>OPERATOR</codeph> clause could include a
          <codeph>RECHECK</codeph> option. This option is no longer supported. Greenplum Database
        now determines whether an index operator is "lossy" on-the-fly at run time. This allows more
        efficient handling of cases where an operator might or might not be lossy.</p>
    </section>
    <section id="section7">
      <title>Examples</title>
      <p>The following example command adds cross-data-type operators and support functions to an
        operator family that already contains B-tree operator classes for data types int4 and int2.: </p>
      <codeblock>ALTER OPERATOR FAMILY integer_ops USING btree ADD

  -- int4 vs int2
  OPERATOR 1 &lt; (int4, int2) ,
  OPERATOR 2 &lt;= (int4, int2) ,
  OPERATOR 3 = (int4, int2) ,
  OPERATOR 4 >= (int4, int2) ,
  OPERATOR 5 > (int4, int2) ,
  FUNCTION 1 btint42cmp(int4, int2) ,

  -- int2 vs int4
  OPERATOR 1 &lt; (int2, int4) ,
  OPERATOR 2 &lt;= (int2, int4) ,
  OPERATOR 3 = (int2, int4) ,
  OPERATOR 4 >= (int2, int4) ,
  OPERATOR 5 > (int2, int4) ,
  FUNCTION 1 btint24cmp(int2, int4) ;</codeblock>
    </section>
    <p>To remove these entries:
      <codeblock>ALTER OPERATOR FAMILY integer_ops USING btree DROP

  -- int4 vs int2
  OPERATOR 1 (int4, int2) ,
  OPERATOR 2 (int4, int2) ,
  OPERATOR 3 (int4, int2) ,
  OPERATOR 4 (int4, int2) ,
  OPERATOR 5 (int4, int2) ,
  FUNCTION 1 (int4, int2) ,

  -- int2 vs int4
  OPERATOR 1 (int2, int4) ,
  OPERATOR 2 (int2, int4) ,
  OPERATOR 3 (int2, int4) ,
  OPERATOR 4 (int2, int4) ,
  OPERATOR 5 (int2, int4) ,
  FUNCTION 1 (int2, int4) ;</codeblock></p><section id="section8"><title>See Also</title><p><codeph><xref href="./CREATE_OPERATOR_FAMILY.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="./DROP_OPERATOR_FAMILY.xml#topic1" type="topic" format="dita"
          /></codeph>, <codeph><xref href="ALTER_OPERATOR_CLASS.xml#topic1" type="topic"
            format="dita"/></codeph>, <codeph><xref href="CREATE_OPERATOR_CLASS.xml#topic1"
            type="topic" format="dita"/></codeph>, <codeph><xref
            href="./DROP_OPERATOR_CLASS.xml#topic1" type="topic" format="dita"/></codeph></p></section></body></topic>
